/**
 * Copyright 2005 SIB Framework

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */
package org.sibframework.sda;

import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.sibframework.utils.ReflectTool;
import org.sibframework.utils.SqlUtility;

/**
 * the main class to implement database access.
 * @author Spring Song
 *
 */
public class SDA {
	private static SDA instance;
	private final JDBC jdbc;
	

	public JDBC getJdbc() {
		return jdbc;
	}

	/**
	 * close this instance
	 */
	public void close() {
		if (this.jdbc != null) {
			this.jdbc.close();
		}
		
		if (instance == this) {
			instance = null;
		}
	}
	
	/**
	 * get singleton instance
	 * @return instance
	 * @throws Exception 
	 */
	public static SDA getInstance() throws Exception {
		if (instance == null || instance.jdbc == null || instance.jdbc.isAvailable()) {
			instance = new SDA();
		}
		
		return instance;
	}
	
	/**
	 * 
	 * @throws Exception 
	 * 
	 */
	private SDA() throws SQLException{
		jdbc = new JDBC();
	}
	

	/**
	 * @param ds datasource
	 * @throws SQLException
	 */
	public SDA(DataSource ds) throws SQLException{
		jdbc = new JDBC(ds);
	}
	
	
	
	// TODO delete
	public Connection getConnection() {
		return jdbc.getConnection();
	}

	// TODO delete
	public boolean isPrintSql() {
		return false;
	}
	
	/**
	 * list all data of a Class, that will query all data in a table.
	 * default without sort by primary key.
	 * @param objectClass
	 * @return all data of a Class
	 */

	public<T> List<T> listAll(Class<T> objectClass) {
		return this.listAll(objectClass, false);
	}
	
	/**
	 * list all data of a Class, that will query all data in a table.
	 * @param objectClass Class of the object.
	 * @param isSort if true, sort by primary key.
	 * @return all data of a Class
	 */
	public<T> List<T> listAll(Class<T> objectClass, boolean isSort) {
		TableProvider tp = TableRegister.getTableProvider(objectClass);
		if (tp == null) {
			return Collections.emptyList();
		}
		String sql = "select * from " + tp.getTableName();
		try {
			Method pkget = objectClass.getMethod(SqlUtility.column2PropertyGetter(tp.getPrimaryKey()), new Class[]{});
			if (isSort) {
				if (pkget.getReturnType() == Integer.class || pkget.getReturnType() == Long.class) {
					sql += " order by ceil(" + tp.getPrimaryKey() + ")"; 
				} else {
					sql += " order by " + tp.getPrimaryKey(); 
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return this.query(sql, objectClass);
	}
	
	/**
	 * find a object by primary key.
	 * @param object object with primary key seted
	 * @return object
	 */
	public<T> T findByPK(T object) {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return null;
		}
		try {
			String sql = "select * from " + tp.getTableName() 
				+ " where " + tp.getPrimaryKey() + " = ";
			Method method = object.getClass().getMethod(SqlUtility.column2PropertyGetter(tp.getPrimaryKey()), new Class[0]);
			if (method.getReturnType() == String.class) {
				sql += "'";
			}
			sql += method.invoke(object, new Object[0]);
			if (method.getReturnType() == String.class) {
				sql += "'";
			}
			@SuppressWarnings("unchecked")
			Class<T> clazz = (Class<T>) object.getClass();
			T result = this.queryFirst(sql, clazz);
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 *
	 * @param object object with primary key seted
	 * @return object
	 */
	/**
	 * find a object by primary key.
	 * @param clazz class to search.
	 * @param ser id as java.io.Serializable.
	 * @return Object or null if not find.
	 */
	public<T> T findByPK(Class<T> clazz, Serializable ser) {
		TableProvider tp = TableRegister.getTableProvider(clazz);
		if (tp == null) {
			return null;
		}
		try {
			String sql = "select * from " + tp.getTableName() 
				+ " where " + tp.getPrimaryKey() + " = ";
			if (ser instanceof String) {
				sql += "'";
			}
			sql += ser;
			if (ser instanceof String) {
				sql += "'";
			}
			T result = this.queryFirst(sql, clazz);
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	/**
	 * search object from database by any give field.
	 * Field is null will not be use.
	 * Field set with value will be used to search.  
	 * @param object with some field set.
	 * @return object list of the result.
	 */
	public<T> List<T> search(T object) {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return Collections.emptyList();
		}
		StringBuffer sql = new StringBuffer("select * from ").append(tp.getTableName());
		sql.append(" where 1=1 ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (o != null) {
					sql.append(" and ");
					sql.append(SqlUtility.propertyGetterIser2Column(getter.getName()));
					sql.append(" = ");
					if (o instanceof String || o instanceof Date) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat("yyyy-MM-dd").format((Date) o));
					} else {
						sql.append(o.toString());
					}
					if (o instanceof String || o instanceof Date) {
						sql.append("'");
					}
				}
			}
		}
		try {
			if (tp.getPrimaryKey() != null) {
				//Method pkget = object.getClass().getMethod(SqlUtility.column2PropertyGetter(tp.getPrimaryKey()), new Class[]{});
				/*if (pkget.getReturnType() == Integer.class || pkget.getReturnType() == Long.class) {
					sql.append(" order by ceil(" + tp.getPrimaryKey() + ")"); 
				} else */{
					sql.append(" order by " + tp.getPrimaryKey()); 
				}
			}
		} catch (Exception e) {
			System.err.println(e.getLocalizedMessage());
		}
		//tp.getPrimaryKey() + " = '" + ReflectTool.getInstance().getPreperty(simpleObject, SqlUtility.column2PropertyGetter(tp.getPrimaryKey())) + "'";
		@SuppressWarnings("unchecked")
		Class<T> clazz = (Class<T>) object.getClass();
		return this.query(sql.toString(), clazz);
	}

	/**
	 * @see org.sibframework.sda.SDA#search(Object object)
	 * Same as search(Object object) but only return the first object. 
	 * @param object
	 * @return first object of the result.
	 */
	public<T> T searchFirst(T object) {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return null;
		}
		StringBuffer sql = new StringBuffer("select * from ").append(tp.getTableName());
		sql.append(" where 1=1 ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				if (o != null) {
					sql.append(" and ");
					sql.append(SqlUtility.propertyGetterIser2Column(getter.getName()));
					sql.append(" = ");
					if (o instanceof String || o instanceof Date) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat("yyyy-MM-dd").format((Date) o));
					} else {
						sql.append(o.toString());
					}
					if (o instanceof String || o instanceof Date) {
						sql.append("'");
					}
				}
			}
		}
		try {
			Method pkget = object.getClass().getMethod(SqlUtility.column2PropertyGetter(tp.getPrimaryKey()), new Class[]{});
			if (pkget.getReturnType() == Integer.class || pkget.getReturnType() == Long.class) {
				sql.append(" order by ceil(" + tp.getPrimaryKey() + ")"); 
			} else {
				sql.append(" order by " + tp.getPrimaryKey()); 
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		//tp.getPrimaryKey() + " = '" + ReflectTool.getInstance().getPreperty(simpleObject, SqlUtility.column2PropertyGetter(tp.getPrimaryKey())) + "'";
		@SuppressWarnings("unchecked")
		Class<T> clazz = (Class<T>) object.getClass();
		return this.queryFirst(sql.toString(), clazz);
	}

	/**
	 * save a object change to database.
	 * @param object
	 * @return count of rows affored.
	 * @throws SQLException 
	 */
	public int update(Object object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("update ").append(tp.getTableName()).append(" set ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				String cn = SqlUtility.propertyGetterIser2Column(getter.getName());
				if (tp.getPrimaryKey().equalsIgnoreCase(cn)) {
					continue;
				}
				sql.append(cn);
				sql.append(" = ");
				if (o == null) {
					sql.append(" null ").append(", ");
				} else {
					if (o instanceof String || o instanceof Date || o instanceof Boolean) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat("yyyy-MM-dd").format((Date) o));
					} else {
						sql.append(prepareString(o));
					}
					if (o instanceof String || o instanceof Date || o instanceof Boolean) {
						sql.append("'");
					}
					sql.append(", ");
				}
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		String pkgetstr = SqlUtility.column2PropertyGetter(tp.getPrimaryKey());
		try {
			@SuppressWarnings("rawtypes")
			Class retype = object.getClass().getMethod(pkgetstr, new Class[0]).getReturnType();
			if (retype == String.class) {
				sql.append(" where ").append(tp.getPrimaryKey()).append(" = '");
				sql.append(ReflectTool.getInstance().getPreperty(object, pkgetstr));
				sql.append("'");
			} else {
				sql.append(" where ").append(tp.getPrimaryKey()).append(" = ");
				sql.append(ReflectTool.getInstance().getPreperty(object, pkgetstr));
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return this.write(sql.toString());
	}
	
	/**
	 * save a object change to database.
	 * @param object
	 * @return count of rows affored.
	 * @throws SQLException 
	 */
	public int update2(Object object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("update ").append(tp.getTableName()).append(" set ");
		Method[] getters = object.getClass().getDeclaredMethods();
		int index = 1;
		Map<Integer, Object> valueMap = new HashMap<Integer, Object>();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				String cn = SqlUtility.propertyGetterIser2Column(getter.getName());
				if (tp.getPrimaryKey().equalsIgnoreCase(cn)) {
					continue;
				}
				sql.append(cn);
				sql.append(" = ?, ");

				valueMap.put(index, o);
				index++;
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		String pkgetstr = SqlUtility.column2PropertyGetter(tp.getPrimaryKey());
		sql.append(" where ").append(tp.getPrimaryKey()).append(" = ?");

		valueMap.put(index, ReflectTool.getInstance().getPreperty(object, pkgetstr));
		index++;

		PreparedStatement pstm = null;
		int count = 0;
		pstm = getConnection().prepareStatement(sql.toString());
		for (int i = 1; i < index; i++) {
			pstm.setObject(i, valueMap.get(i));
		}
		count = pstm.executeUpdate();
		pstm.close();
		
		return count;
		
	}
	
	/**
	 * delete a object from database.
	 * @param object the object to delete
	 * @return the count has deleted.
	 * @throws SQLException 
	 * @throws NoSuchMethodException 
	 * @throws SecurityException 
	 */
	public int delete(Object object) throws SQLException {
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}

		String pkgetstr = SqlUtility.column2PropertyGetter(tp.getPrimaryKey());
		String sql;
		
		@SuppressWarnings("rawtypes")
		Class retype = null;
		try {
			retype = object.getClass().getMethod(pkgetstr, new Class[0]).getReturnType();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getLocalizedMessage());
		}
		sql = "delete from " + tp.getTableName();
		sql += " where " + tp.getPrimaryKey() + " = "; 
		if (retype == String.class) {
			sql += "'" + ReflectTool.getInstance().getPreperty(object, SqlUtility.column2PropertyGetter(tp.getPrimaryKey())) + "'";
		} else {
			sql += ReflectTool.getInstance().getPreperty(object, SqlUtility.column2PropertyGetter(tp.getPrimaryKey()));
		}
		
		return this.write(sql);
	}

	/**
	 * insert a object to database.
	 * @param object the object to insert.
	 * @return the number has inserted.
	 * @throws SQLException 
	 */
	public int insert(Object object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("insert into ").append(tp.getTableName()).append(" ( ");
		Method[] getters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				//TODO there should has a dicision to skip column when getter return null;
				sql.append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", ");
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		sql.append(") values (");
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			Object o = null;
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				try {
					o = getter.invoke(object, new Object[]{});
				} catch (Exception e) {
					e.printStackTrace();
				}
				//TODO there should has a dicision to skip column when getter return null;
				if (o == null) {
					sql.append(" null ").append(", ");
				} else {
					if (o instanceof String || o instanceof Date || o instanceof Boolean) {
						sql.append(" '");
					}
					if (o instanceof Date) {
						sql.append(new SimpleDateFormat("yyyy-MM-dd").format((Date) o));
					} else {
						sql.append(prepareString(o));
					}
					if (o instanceof String || o instanceof Date|| o instanceof Boolean) {
						sql.append("'");
					}
					sql.append(", ");
				}
			}
		}
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		sql.append(")");
		return this.write(sql.toString());
	}

	/**
	 * insert a object to database.
	 * @param object the object to insert.
	 * @return the number has inserted.
	 * @throws SQLException 
	 */
	public int insert2(Object object) throws SQLException{
		TableProvider tp = TableRegister.getTableProvider(object.getClass());
		if (tp == null) {
			return 0;
		}
		StringBuffer sql = new StringBuffer("insert into ").append(tp.getTableName()).append(" ( ");
		Method[] getters = object.getClass().getDeclaredMethods();
		
		StringBuffer values = new StringBuffer();
		int index = 1;
		Map<Integer, Object> valueMap = new HashMap<Integer, Object>();
		for (int i = 0; i < getters.length; i++) {
			Method getter = getters[i];
			if (getter.getName().startsWith("get") || getter.getName().startsWith("is")) {
				
				sql.append(SqlUtility.propertyGetterIser2Column(getter.getName())).append(", ");
				values.append("?, ");
				try {
					Object o = getter.invoke(object, new Object[]{});
					valueMap.put(index, o);
					index++;
				} catch (Exception e) {
					e.printStackTrace();
				}
				
			}
		}
		
		
		if (sql.toString().endsWith(", ")) {
			sql.delete(sql.length() - 2, sql.length());
		}
		if (values.toString().endsWith(", ")) {
			values.delete(values.length() - 2, values.length());
		}
		sql.append(") values (").append(values).append(")");
		

		PreparedStatement pstm = null;
		int count = 0;
		pstm = getConnection().prepareStatement(sql.toString());
		for (int i = 1; i < index; i++) {
			pstm.setObject(i, valueMap.get(i));
		}
		count = pstm.executeUpdate();
		pstm.close();
		
		return count;
		
	}
	
	private String prepareString(Object o) {
		if (o == null) {
			return "";
		}
		String str = o.toString().trim();
		str = str.replace("\\", "\\\\");
		str = str.replace("'", "''");
		return str;
	}
	/**
	 * get the max primary key from database. used to set new object primary key.
	 * @param objectClass the object to get max primary key of;
	 * @return the max primary key of current objects.
	 * TODO need to make sure the synchronized.
	 */
	public synchronized long getMaxPk(@SuppressWarnings("rawtypes") Class objectClass) {
		TableProvider tp = TableRegister.getTableProvider(objectClass);
		if (tp == null) {
			return -1;
		}
		String sql = "select max(" + tp.getPrimaryKey() + ") from " + tp.getTableName();

		String spk = queryString(sql);
		if (spk != null && spk.length() > 0) {
			return (long) Float.parseFloat(spk);
		};
		return 0;
	}
	
	
	/**
	 * custom execute a write SQL.
	 * @param sql The SQL string to execute.
	 * @return operator Count of rows affected.
	 * @throws SQLException 
	 */
	public int write(String sql) throws SQLException {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		int i = 0;
		pstm = getConnection().prepareStatement(sql);
		i = pstm.executeUpdate();
		pstm.close();
		return i;
	}
	
	/**
	 * custom execute a series of write SQLs in one transaction. Roll back if any SQL failed.
	 * @param sqls The SQL string list to execute.
	 * @return operator row count.
	 */
	public boolean transactionWrite(List<String> sqls) {
		Statement stm = null;
		try {
			getConnection().setAutoCommit(false);
			stm = getConnection().createStatement();
			
			for (String sql :sqls) {
				if (isPrintSql()) {
					System.out.println(sql);
				}
				stm.executeUpdate(sql);
			}
			getConnection().commit();
			getConnection().setAutoCommit(true);
			stm.close();
		} catch (SQLException e) {
			try {
				if (stm != null) {
					stm.close();
				}
				getConnection().rollback();
				getConnection().setAutoCommit(true);
				return false;
			} catch (Exception e1) {}
			System.err.println(sqls);
			e.printStackTrace();
		}
		return true;
	}
	/**
	 * query a single string from database.
	 * @param sql string to query
	 * @return String, the first column of first object of the search result.
	 */
	public String queryString(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String value = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			if (rs.next()) {
				value = rs.getString(1);
			}
			rs.close();
			pstm.close();
			return value;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		return value;
	}
	/**
	 * query a single string from database.
	 * @param sql string to query
	 * @return String, the first column of first object of the search result.
	 */
	public Object queryValue(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Object value = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			if (rs.next()) {
				value = rs.getObject(1);
			}
			rs.close();
			pstm.close();
			return value;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		return value;
	}
	
	/**
	 * query a single string from database.
	 * @param sql string to query
	 * @return the first column of objects of the search result.
	 */
	public List<String> queryStrings(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<String> values = new ArrayList<String>();
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				String value = rs.getString(1);
				values.add(value);
			}
			rs.close();
			pstm.close();
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		return values;
	}

	/**
	 * query the custom SQL and return the first object of result
	 * @param sql String to query
	 * @param objectClass Class the result class type.
	 * @return first object of the search result.
	 */
	public<T> T queryFirst(String sql, Class<T> objectClass) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			T object = null;
			if (rs.next()) {
				object = objectClass.newInstance();
				this.feedObject2(rs, object);
			}
			rs.close();
			pstm.close();
			return object;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
			
		}
		return null;
	}

	/**
	 * custom query a SQL from database and cast the result to object for give Class 
	 * @param sql sql to query
	 * @param objectClass the result object class type
	 * @return query result, make of objects of the give class.
	 */
	public<T> List<T> query(String sql, Class<T> objectClass) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			List<T>  results = new ArrayList<T>();
			while (rs.next()) {
				T object = objectClass.newInstance();
				this.feedObject2(rs, object);
				results.add(object);
			}
			rs.close();
			pstm.close();
			return results;
			
		} catch (Exception e) {
			
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	
	/**
	 * feed object use a rs.
	 * @param rs
	 * @param object
	 */
	private void feedObject2(ResultSet rs, Object object) {
		for (PropertyDescriptor pd : PropertyUtils.getPropertyDescriptors(object)) {
			try {
				Object o = rs.getObject(SqlUtility.property2Column(pd.getName()));
				if (o != null) {
					//预处理Oracle的TIMESTAMP类型。
					if (o instanceof oracle.sql.TIMESTAMP) {
						oracle.sql.TIMESTAMP t = (oracle.sql.TIMESTAMP) o;
						o = t.timestampValue();
					}
					
					if (pd.getPropertyType().isInstance(o)) {
						PropertyUtils.setProperty(o, pd.getName(), o);
					} else {
						BeanUtils.setProperty(object, pd.getName(), o.toString());
					}
				}
			} catch (SQLException e) {
			} catch (IllegalAccessException e) {
			} catch (InvocationTargetException e) {
			} catch (NoSuchMethodException e) {
			}
		}
	}
	
	/**
	 * feed object use a rs.
	 * @param rs
	 * @param object
	 */
	@SuppressWarnings("unused")
	private void feedObject(ResultSet rs, Object object) {
		Method[] setters = object.getClass().getDeclaredMethods();
		for (int i = 0; i < setters.length; i++) {
			Method setter = setters[i];
			if (setter.getName().startsWith("set")) {
				Object[] parameters = new Object[1];
				String value = null;
				Object data = null;
				try {
					value = rs.getString(SqlUtility.propertySetter2Column(setter.getName()));
					data =  rs.getObject(SqlUtility.propertySetter2Column(setter.getName()));
				} catch (Exception e) {
					System.err.println(e.getLocalizedMessage());
					continue;
				}
				if (setter.getParameterTypes()[0].isInstance(data)) {
					parameters[0] =  data;
				} else if (setter.getParameterTypes()[0].equals(String.class)) {
					parameters[0] =  value;
				} else if (setter.getParameterTypes()[0].equals(Long.class) || setter.getParameterTypes()[0].equals(long.class)){
					parameters[0] =  new Long(value);
				} else if (setter.getParameterTypes()[0].equals(Integer.class) || setter.getParameterTypes()[0].equals(int.class)){
					parameters[0] =  new Integer(value);
				}else if (setter.getParameterTypes()[0].equals(Boolean.class) || setter.getParameterTypes()[0].equals(boolean.class)){
					parameters[0] =  new Boolean(value);
				} else if (setter.getParameterTypes()[0].equals(Float.class) || setter.getParameterTypes()[0].equals(float.class)){
					parameters[0] =  new Float(value);
				} else if (setter.getParameterTypes()[0].equals(Double.class) || setter.getParameterTypes()[0].equals(double.class)){
					parameters[0] =  new Double(value);
				} else if (setter.getParameterTypes()[0].equals(Date.class)){
					try {
						parameters[0] =  new SimpleDateFormat("yyyy-MM-dd").parse(value);
					} catch (ParseException e) {
					}
				} 
				if (parameters[0] != null) {
					try {
						setter.invoke(object, parameters);
					} catch (IllegalAccessException e) {
					} catch (IllegalArgumentException e) {
					} catch (InvocationTargetException e) {
					}
				}
			}
		}
	}
}
